<?php
/**
 * Created by PhpStorm.
 * User: zlkmu
 * Date: 2017/8/14
 * Time: 14:39
 */
    require_once ('../Common.php');
    require_once ('../SQLBase.php');
    require_once ('../LoanFee/LoanFee.php');
    require_once ('../FinanceFeeManage/FinanceFeeManage.php');
    class OrdinaryFee{


        //辅助函数，作废历史报销数据
        function disposeOrdinaryFeeApprove($history_id){
            if($history_id == ''){
                return true;
            }
            $conn = new SQLBase();
            $sql = "UPDATE ordinary_fee_approve SET is_dispose = 1 WHERE fee_id = '$history_id'";
            $r = $conn->queryData($sql);
            if($r){
                return true;
            }
            else{
                return false;
            }
        }

        //辅助函数,创建业务报销数据
        //1 作废历史数据
        //2 创建新数据
        function createOrdinaryFeeApprove($data){
            $conn = new SQLBase();
            $conn->queryData('SET AUTOCOMMIT=0');
            $conn->queryData('BEGIN');

            $r = $this->disposeOrdinaryFeeApprove($data['history_fee_id']);
            if(!$r){
                $conn->queryData('ROLLBACK');
                $conn->queryData('SET AUTOCOMMIT=1');
                return array('state'=>0, 'error'=>'DISPOSE_ERROR');
            }

            $id = uniqid();
            $sql = "INSERT INTO ordinary_fee_approve(fee_id, write_ID, section_ID, pre_total_fee, loan_fee_id, history_fee_id, cash, trans_fee, official_fee, write_time) 
                      VALUES('$id','".$data['user_id']."','".$data['section_id']."','"
                        .$data['total_money']."','".$data['loanFeeID']."','".$data['history_fee_id']."','"
                        .$data['cash']."','".$data['trans']."','".$data['official']."',NOW())";

            $r = $conn->queryData($sql);
            if(!$r){
                $conn->queryData('ROLLBACK');
                $result_array['state'] = 0;
                $result_array['error'] = 'INSERT_ODRINARY_FEE_APP_ERROR';
            }
            else{
                $fee_detail = $data['data'];
                for($i = 0; $i < count($fee_detail); $i++){
                    $sql = "INSERT INTO ordinary_fee_detail(fee_id, fee_item, fee_type, money) 
                      VALUES('$id','".$fee_detail[$i]['fee_item']."','".$fee_detail[$i]['fee_type']."','".$fee_detail[$i]['money']."')";
                    $r = $conn->queryData($sql);
                    if(!$r){
                        $conn->queryData('ROLLBACK');
                        $result_array['state'] = 0;
                        $result_array['error'] = 'INSERT_ORDINARY_FEE_ITEM_ERROR';
                        return $result_array;
                    }
                }
                $conn->queryData('COMMIT');
                $result_array['state'] = 1;
            }
            return $result_array;
        }

        //辅助函数，获取业务报销基本费用信息，根据部门或多个部门，以及状态
        function getOrdinaryFeeApproveBySection($sectionID, $allsection, $state){
            if($allsection == 0){
                $sql = "SELECT ordinary_fee_approve.*, allsection.section_name, user.user_name FROM ordinary_fee_approve, allsection, user 
                      WHERE ordinary_fee_approve.state_detail = '$state' AND ordinary_fee_approve.state = 0 AND allsection.section_ID = ordinary_fee_approve.section_ID  
                      AND ordinary_fee_approve.section_ID = '$sectionID' AND ordinary_fee_approve.is_dispose = 0
                      AND user.user_id = ordinary_fee_approve.write_ID";
            }
            else{
                $sql = "SELECT ordinary_fee_approve.*, allsection.section_name, user.user_name FROM ordinary_fee_approve, allsection, user 
                      WHERE ordinary_fee_approve.state_detail = '$state' AND ordinary_fee_approve.state = 0 AND allsection.section_ID = ordinary_fee_approve.section_ID 
                      AND ordinary_fee_approve.is_dispose = 0 AND user.user_id = ordinary_fee_approve.write_ID AND ordinary_fee_approve.section_ID IN (";
                for($i = 0; $i < count($sectionID); $i++){
                    $sql .= "'$sectionID[$i]'";
                    if($i != count($sectionID)-1){
                        $sql .= ",";
                    }
                    else{
                        $sql .= ")";
                    }
                }
            }
            $conn = new SQLBase();
            $r = $conn->queryData($sql);
            $result_array = array();
            if($r){
                $result_array['state'] = 1;
                $result_array['data'] = array();
                for($i = 0; $i < $r->num_rows; $i++){
                    $row = $r->fetch_array();
                    $result_array['data'][$i] = array('type'=>2, 'fee_id'=>$row['fee_id'], 'user_name'=>$row['user_name'], 'pre_total_fee'=>$row['pre_total_fee'],
                            'section_name'=>$row['section_name'], 'app_time'=>$row['write_time']);
                }
                $result_array['total_count'] = $r->num_rows;
            }
            else{
                $result_array['state'] = 0;
                $result_array['error'] = 'DB_ERROR';
            }
            return $result_array;
        }

        //辅助函数，获取业务报销基本数据，根据用户ID，状态
        function getOrdinaryFeeApprove($user_id, $state){
            $conn = new SQLBase();
            $sql = "SELECT ordinary_fee_approve.*, allsection.section_name, user.user_name FROM ordinary_fee_approve, allsection, user 
                      WHERE ordinary_fee_approve.write_ID = '$user_id' AND allsection.section_ID = ordinary_fee_approve.section_id 
                      AND user.user_id = ordinary_fee_approve.write_ID AND is_dispose = 0";
            //如果不是全部状态，则为0待审批，1已同意 2已驳回,3已报销
            if($state != 4){
                $sql .= " AND ordinary_fee_approve.state='$state' ORDER BY write_time DESC";
            }//否则查询全部
            else{
                $sql .= " ORDER BY write_time DESC";
            }
            $r = $conn->queryData($sql);
            $result_array = array();
            if($r){
                $result_array['totalCount'] = $r->num_rows;
                $result_array = array();
                if($r){
                    $result_array['state'] = 1;
                    $result_array['data'] = array();
                    for($i = 0; $i < $r->num_rows; $i++){
                        $row = $r->fetch_array();
                        $result_array['data'][$i] = array('fee_id'=>$row['fee_id'],
                            'write_id'=>$row['write_ID'], 'section_id'=>$row['section_id'], 'user_name'=>$row['user_name'], 'pre_total_fee'=>$row['pre_total_fee'],
                            'section_name'=>$row['section_name'], 'state'=>$row['state'], 'state_detail'=>$row['state_detail'], 'section_manager_ID'=>$row['section_manager_ID'],
                            'section_leader_ID'=>$row['section_leader_ID'], 'leader_ID'=>$row['leader_ID'], 'write_time'=>$row['write_time'],
                            'section_approve_opinion'=>$row['section_approve_opinion'], 'section_leader_opinion'=>$row['section_leader_opinion'],
                            'leader_opinion'=>$row['leader_opinion']);
                    }
                }else{
                    $result_array['state'] = 0;
                    $result_array['error'] = 'DB_ERROR';
                }
            }
            else{
                $result_array['state'] = 0;
                $result_array['error'] = 'DB_ERROR';
            }
            return $result_array;
        }

        //辅助函数，获取详细业务报销数据，根据报销ID
        function getOrdinaryFeeDetailByID($appID){
            $conn = new SQLBase();
            $sql = "SELECT ordinary_fee_approve.*, allsection.section_name, user.user_name FROM ordinary_fee_approve, allsection, user  
                      WHERE  allsection.section_ID = ordinary_fee_approve.section_id AND user.user_id = ordinary_fee_approve.write_ID AND ordinary_fee_approve.fee_id='$appID' ";
            $r = $conn->queryData($sql);

            if($r){
                $row = $r->fetch_array();
                $result_array = array('fee_id'=>$row['fee_id'], 'loan_fee_id'=>$row['loan_fee_id'],
                    'write_id'=>$row['write_ID'], 'section_id'=>$row['section_ID'], 'user_name'=>$row['user_name'],
                    'pre_total_fee'=>$row['pre_total_fee'], 'section_name'=>$row['section_name'],
                    'state'=>$row['state'], 'state_detail'=>$row['state_detail'], 'section_manager_ID'=>$row['section_manager_ID'],
                    'section_leader_ID'=>$row['section_leader_ID'], 'leader_ID'=>$row['leader_ID'], 'write_time'=>$row['write_time'],
                    'section_approve_opinion'=>$row['section_approve_opinion'], 'section_leader_opinion'=>$row['section_leader_opinion'],
                    'leader_opinion'=>$row['leader_opinion'], 'history_fee_id'=>$row['history_fee_id'], 'cash'=>$row['cash'],
                    'trans'=>$row['trans_fee'], 'official'=>$row['official_fee']);

                if($row['loan_fee_id'] != ''){
                    $sql = "SELECT * FROM loan_money WHERE loan_money.loan_money_ID = '".$row['loan_fee_id']."'";
                    $r = $conn->queryData($sql);
                    if($r){
                        if($r->num_rows > 0){
                            $result_array['loan_fee'] = $r->fetch_array()['loan_money'];
                        }
                        else{
                            $result_array['loan_fee'] = 0;
                        }
                    }
                    else{
                        return false;
                    }
                }
                else{
                    $result_array['loan_fee'] = 0;
                }

                $sql = "SELECT finance_approve_ordinary_fee.* FROM finance_approve_ordinary_fee WHERE approve_id = '$appID'";
                $r = $conn->queryData($sql);
                if($r){
                    if($r->num_rows > 0){
                        $row = $r->fetch_array();
                        $result_array['fuhe_total_fee'] = $row['total_fee'];
                        $result_array['fuhe_cash'] = $row['cash'];
                        $result_array['fuhe_trans_fee'] = $row['trans_fee'];
                        $result_array['fuhe_official'] = $row['official_fee'];
                    }
                    else{
                        $result_array['fuhe_total_fee'] = 0;
                        $result_array['fuhe_cash'] = 0;
                        $result_array['fuhe_trans_fee'] = 0;
                        $result_array['fuhe_official'] = 0;
                    }
                }
                else{
                    return false;
                }


                $sql = "SELECT ordinary_fee_detail.*, fee_item.item_name FROM ordinary_fee_detail, fee_item WHERE ordinary_fee_detail.fee_id = '$appID' AND fee_item.item_id = ordinary_fee_detail.fee_type";
                $r = $conn->queryData($sql);
                if($r){
                    for($i = 0; $i < $r->num_rows; $i++){
                        $row = $r->fetch_array();
                        $result_array['data'][$i] = array('fee_item_id'=>$row['Id'], 'fee_item'=>$row['fee_item'], 'fee_type'=>$row['fee_type'], 'fee_type_name'=>$row['item_name'], 'money'=>$row['money'], 'fuhe_money'=>$row['fuhe_money']);
                    }
                    return $result_array;
                }
                else{
                    return false;
                }
            }
            return false;
        }

        //辅助函数，审批普通业务报销数据
        function approveOrdinaryFee($data){
            $conn = new SQLBase();
            $sql = '';
            $userID = $data['userID'];
            $opinion = $data['ordinay_fee_opinion']['basic_fee_opinion'];
            $opinion_detail = $data['ordinay_fee_opinion']['basic_fee_opinion_detail'];
            $appID = $data['approve_id'];
            $type = $data['approve_state'];
            if ($opinion == 1) {
                switch ($type) {
                    case 1:
                        $sql = "UPDATE ordinary_fee_approve SET section_manager_ID='$userID', state_detail='02', section_approve_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                    case 2:
                        $sql = "UPDATE ordinary_fee_approve SET section_leader_ID='$userID', state_detail='03', section_leader_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                    case 3:
                        $sql = "UPDATE ordinary_fee_approve SET leader_ID='$userID', state='1', state_detail='11', leader_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                }
            } else {
                switch ($type) {
                    case 1:
                        $sql = "UPDATE ordinary_fee_approve SET section_manager_ID='$userID', state='2', state_detail='21', section_approve_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                    case 2:
                        $sql = "UPDATE ordinary_fee_approve SET section_leader_ID='$userID', state='2', state_detail='22', section_leader_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                    case 3:
                        $sql = "UPDATE ordinary_fee_approve SET leader_ID='$userID', state='2', state_detail='23', leader_opinion='$opinion_detail' WHERE fee_id='$appID'";
                        break;
                }
            }
            $r = $conn->queryData($sql);
            if($r){
                return true;
            }
            else{
                return false;
            }

        }

        //辅助函数，获取财务复核所需数据
        function getFinanceOrdinaryFeeApprove($sectionID, $username){
            $sql = "SELECT ordinary_fee_approve.*, allsection.section_name, user.user_name FROM ordinary_fee_approve INNER JOIN allsection, user  
                  WHERE ordinary_fee_approve.section_ID = allsection.section_ID AND ordinary_fee_approve.write_ID = user.user_ID AND ordinary_fee_approve.state ='1'";


            if($sectionID != 0){
                $sql .= " AND ordinary_fee_approve.section_ID = '$sectionID'";
            }
            if($username != ''){
                $sql .= " AND user.user_name LIKE '$username%'";
            }
            $conn = new SQLBase();
            $result_array = array();
            $r = $conn->queryData($sql);
            if($r){
                $result_array['data'] = array();
                $result_array['state'] = 1;
                $result_array['state_error'] = '';
                for($i = 0; $i < $r->num_rows; $i++){
                    $row = $r->fetch_array();
                    $result_array['data'][$i] = array('type'=>2, 'user_name'=>$row['user_name'], 'section_name'=>$row['section_name'], 'app_time'=>$row['write_time'],
                        'app_id'=>$row['fee_id'], 'pre_total_fee'=>$row['pre_total_fee'], 'loan_fee_id'=>$row['loan_fee_id']);
                }
                $result_array['total_count'] = $r->num_rows;

            }
            else{
                $result_array['state'] = 0;
                $result_array['state_error'] = 'DB_ERROR';
            }
            return $result_array;
        }

        //辅助函数，用于业务报销的财务复核
        //1 插入finance_approve_ordinary_fee
        //2 更新ordinary_fee_approve 状态
        //3 更新ordinary_fee_detail的fuhe_money状态
        //4 处理借款状态
        //5 核减部门经费
        function financeOrdinaryCheck($data){
            $conn = new SQLBase();
            $conn->queryData('SET AUTOCOMMIT=0');
            $conn->queryData("BEGIN");
            $result_array = array();
            //插入复核业务报销信息
            $sql = "INSERT INTO finance_approve_ordinary_fee(approve_id, write_id, total_fee, cash, trans_fee, official_fee, fuhe_time) 
                      VALUES('". $data['app_id']."','".$data['user_id']."','".$data['fuhe_total_fee']
                                ."','".$data['cash']."','".$data['trans']."','".$data['official']."', NOW())";
            $r = $conn->queryData($sql);
            if($r){
                $fee_id = $data['app_id'];
                $sql = "UPDATE ordinary_fee_approve SET state = 3 WHERE fee_id = '$fee_id'";
                $r = $conn->queryData($sql);
                if($r){
                    for($i = 0; $i < count($data['fuhe_detail']); $i++){
                        $fuhe_money = $data['fuhe_detail'][$i]['fuhe_money'];
                        $fuhe_item_id = $data['fuhe_detail'][$i]['fee_item_id'];
                        $sql = "UPDATE ordinary_fee_detail SET fuhe_money=$fuhe_money WHERE Id = '$fuhe_item_id' AND fee_id = '$fee_id'";
                        $r = $conn->queryData($sql);
                        if(!$r){
                            $conn->queryData('ROLLBACK');
                            $conn->queryData('SET AUTOCOMMIT=1');
                            $result_array['state'] = 0;
                            $result_array['error'] = 'INSERT_ORDINARY_FEE_DETAIL_ERROR';
                            exit(json_encode($result_array));
                        }
                    }
                    $loan_money_id = $data['loan_fee_id'];
                    if($loan_money_id != ''){
                        $l_fee = new LoanFee();
                        $r = $l_fee->financeCheckLoanFee($loan_money_id, $data['fuhe_total_fee'],$data['user_id']);

                        if(!$r){
                            $conn->queryData('ROLLBACK');
                            $conn->queryData('SET AUTOCOMMIT=1');
                            $result_array['state'] = 0;
                            $result_array['error'] = 'UPDATE_LOAN_MONEY_ERROR';
                            exit(json_encode($result_array));
                        }
                    }
                    //核减部门经费
                    $fmm = new FinanceFeeManage();
                    $r = $fmm->subSectionFee($fee_id);
                    if(!$r){
                        $conn->queryData('ROLLBACK');
                        $conn->queryData('SET AUTOCOMMIT=1');
                        $result_array['state'] = 0;
                        $result_array['error'] = 'SUB_SECTION_FEE_ERROR';
                        exit(json_encode($result_array));
                    }

                    $conn->queryData('COMMIT');
                    $result_array['state'] = 1;
                }
                else{
                    $conn->queryData('ROLLBACK');
                    $result_array['state'] = 0;
                }
            }
            else{
                $conn->queryData('ROLLBACK');
                $result_array['state'] = 0;
            }
            $conn->queryData('SET AUTOCOMMIT=1');
            return $result_array;

        }

        //辅助函数，财务驳回业务报销数据
        function refuseOrdinaryFeeApproveByFinance($feeid){
            $conn = new SQLBase();
            $query = "UPDATE ordinary_fee_approve SET state = '2', state_detail = '24' WHERE fee_id = '$feeid' ";
            $r = $conn->queryData($query);
            if($r){
                return true;
            }
            else{
                return false;
            }
        }


    }



?>